﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;

namespace Batman.SQLScripts
{
    public static class SQLScript
    {
        /// <summary>
        /// SQL脚本根路径
        /// </summary>
        public static string ScriptPath { get; set; }

        public static List<Mapping> Maps { get; private set; } = new List<Mapping>();

        /// <summary>
        /// 加载脚本数据
        /// </summary>
        public static void LoadScripts()
        {
            if (!Directory.Exists(ScriptPath)) return;
            if (Maps.Count > 0) return;

            Maps = LoadMappings();
        }

        private static List<Mapping> LoadMappings(bool minifier = true)
        {
            var files = Directory.GetFiles(ScriptPath, "*", SearchOption.AllDirectories);
            List<Mapping> mappings = new List<Mapping>();

            foreach (var file in files)
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);

                XmlNodeList mappingNodeList = doc.SelectNodes("/mappings/mapping");
                if (mappingNodeList == null) continue;

                foreach (XmlNode mappingNode in mappingNodeList)
                {
                    var mapping = new Mapping();
                    mappings.Add(mapping);

                    mapping.Name = mappingNode.Attributes["name"].Value.Trim();
                    mapping.Schemas = new List<Schema>();

                    XmlNodeList schemaNodeList = mappingNode.SelectNodes("schema");

                    foreach (XmlNode schemaNode in schemaNodeList)
                    {
                        Schema schema = new Schema();
                        mapping.Schemas.Add(schema);

                        schema.TableName = schemaNode.Attributes["table_name"]?.Value;
                        schema.Items = new List<SchemaItem>();

                        XmlNodeList schemaItemNodeList = schemaNode.SelectNodes("item");
                        foreach (XmlNode schemaItemNode in schemaItemNodeList)
                        {
                            SchemaItem schemaItem = new SchemaItem();
                            schema.Items.Add(schemaItem);

                            schemaItem.Name = schemaItemNode.Attributes["name"]?.Value;
                            schemaItem.Remark = schemaItemNode.Attributes["remark"]?.Value;
                        }

                        XmlNode schemaCommandNode = schemaNode.SelectSingleNode("command");
                        if (schemaCommandNode == null) continue;

                        SchemaCommand schemaCommand = new SchemaCommand();
                        schema.Command = schemaCommand;

                        schemaCommand.Text = schemaCommandNode.InnerText;
                    }
                }
            }

            if (!minifier) return mappings;

            foreach (var mapping in mappings)
            {
                RemoveComments(mapping);
                Minifier(mapping);
            }

            return mappings;
        }

        /// <summary>
        /// 删除SQL语句中的注释
        /// </summary>
        private static void RemoveComments(Mapping mapping)
        {
            if (mapping == null || mapping.Schemas == null) return;

            foreach (var schema in mapping.Schemas)
            {
                if (schema.Command == null) continue;
                if (string.IsNullOrEmpty(schema.Command.Text)) continue;

                var content = schema.Command.Text;
                var lines = new List<string>();

                using (var mm = new MemoryStream(Encoding.UTF8.GetBytes(content)))
                {
                    using (StreamReader sr = new StreamReader(mm))
                    {
                        while (sr.Peek() >= 0)
                        {
                            var line = sr.ReadLine();
                            line = line.Trim();
                            // MYSQL语法 
                            // 去掉#号开头的
                            if (line.StartsWith("#")) continue;
                            var index = line.IndexOf('#');
                            if (index > -1)
                                // 截断#号和后面的字符
                                line = line.Substring(0, index);

                            lines.Add(line);
                        }
                    }
                }

                schema.Command.Text = string.Join(" ", lines);
            }
        }

        /// <summary>
        /// 处理多余的空格和回车
        /// </summary>
        /// <param name="mapping"></param>
        private static void Minifier(Mapping mapping)
        {
            if (mapping == null || mapping.Schemas == null) return;

            foreach (var schema in mapping.Schemas)
            {
                if (schema.Command == null) continue;
                if (string.IsNullOrEmpty(schema.Command.Text)) continue;

                var content = schema.Command.Text;

                var matches = Regex.Matches(content, @"'(.*)?'");
                var temporaries = new List<NameValueDto<string>>();

                for (int i = 0; i < matches.Count; i++)
                {
                    var match = matches[i];
                    var key = "$" + i.ToString();
                    var value = match.Groups[0].Value;
                    content = content.Replace(value, key);
                    temporaries.Add(new NameValueDto<string>(key, value));
                }

                var newStr = Regex.Replace(content, @"\s+", " ");

                temporaries.ForEach(t => newStr = newStr.Replace(t.Name, t.Value));

                schema.Command.Text = newStr.Trim();
            }
        }

        public static string GetSQL(string key)
        {
            var found = Maps.Where(t => t.Name == key).FirstOrDefault();
            if (found == null) return null;

            return found.Schemas.FirstOrDefault()?.Command?.Text;
        }

        public static Mapping GetMapping(string key)
        {
            var found = Maps.Where(t => t.Name == key).FirstOrDefault();
            if (found == null) return null;

            return found;
        }


    }

    public class ScriptOptions
    {
        public string DirectoryFullPath { get; set; }
    }
}
